<?php if (!defined("_ECRIRE_INC_VERSION")) return; if (class_exists('ProductExcel')) return;
/**
 * EcataloguE
 *
 * @author     Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com)
 * @license    GNU/GPL
 * @package    plugins
 * @subpackage ecatalogue
 * @copyright  (c) Aleksey Yaroshchuk 2011-2012
 * @link       http://www.spip-contrib.net/EcataloguE
 */



/**
 *  This class maintain Excel download/upload operations with products 
 *
 *  @author Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com) 
 */ 
class ProductExcel {   
    private $product_mdl;
    private $article_mdl;
    private $rubrique_mdl;
    
    public function __construct() {
        require_once(_DIR_PLUGIN_ECATALOGUE.'classes/class.ProductModel.php');
        $this->product_mdl = new ProductModel();
        
        require_once(_DIR_PLUGIN_PDK.'classes/models/PdkArticleModel.php');
        $this->article_mdl = new PdkArticleModel();
        
        require_once(_DIR_PLUGIN_PDK.'classes/models/PdkRubriqueModel.php');
        $this->rubrique_mdl = new PdkRubriqueModel();
        
        require_once _DIR_PLUGIN_EXCEL."pear/Spreadsheet/Excel/Writer.php";
    }
    
    public function OutputExcel() {
        // Creating a workbook
        $workbook = new Spreadsheet_Excel_Writer();    
        $workbook->setTempDir(_DIR_CACHE);
        $workbook->setVersion(8);
        
        // sending HTTP headers
        $workbook->send('products.xls');
    
        // Let's create catalogue worksheet:
        $this->addWorksheet($workbook);
        
        // Let's send the file
        $workbook->close();
    
        // After file has been sent, just exit:
        exit;
    }
    
    public function BackupExcel() {
        // Creating a workbook
        $workbook = new Spreadsheet_Excel_Writer(_DIR_TMP.'products_'.date("d-m-Y_H-i-s").'.xls');    
        
        $workbook->setVersion(8);
    
        // Let's create catalogue worksheet:
        $this->addWorksheet($workbook);
        
        // Let's send the file
        $workbook->close();
    
        // After file has been sent, just exit:
        //exit;
    }
    
    /**
     *  Adds Worksheet with clients to given $workbook
     */
    private function addWorksheet(&$workbook) {
        // Creating worksheet    
        $worksheet =& $workbook->addWorksheet('Products');
        $worksheet->setInputEncoding('utf-8');
        
        // Format for first line
        $format_bold =& $workbook->addFormat();
        $format_bold->setBold();
        
        $worksheet->write(0, 0, _T('ecatalogue:id_article'), $format_bold);
        $worksheet->write(0, 1, _T('ecatalogue:sku'), $format_bold);
        $worksheet->write(0, 2, _T('ecatalogue:product_name'), $format_bold);
        $worksheet->write(0, 3, _T('ecatalogue:quantity'), $format_bold);
        $worksheet->write(0, 4, _T('ecatalogue:price'), $format_bold);
        $worksheet->write(0, 5, _T('ecatalogue:priceold'), $format_bold);
        
        
        // Set columns width: 
        $worksheet->setColumn(0,5, 15);
        
        $articles = $this->article_mdl->select("statut!='poubelle'",'','id_article');
        
        include_spip('spip_bonux_fonctions');
        $download_rubriques = picker_selected(_request('download_rubriques'), 'rubrique');
        if (!$download_rubriques)
            $download_rubriques = picker_selected(lire_config('ecatalogue/branches'), 'rubrique');     
                
        $row_num = 1;
        foreach ($articles as $article) {
            
            $download_rubrique = false;
            if (isset($download_rubriques) && is_array($download_rubriques))
                foreach ($download_rubriques as $id_rubrique) {
                    if ($this->rubrique_mdl->CheckRubriqueInRubrique($article['id_rubrique'], $id_rubrique) || $article['id_rubrique'] == $id_rubrique)
                        $download_rubrique = true;
                }
            if (!isset($download_rubriques) || !is_array($download_rubriques) || empty($download_rubriques) ||
                $download_rubrique) {
                $product = $this->product_mdl->select_by_id($article['id_article'], true);    
                $worksheet->write($row_num, 0, $article['id_article']);
                $worksheet->write($row_num, 1, $product['sku']);
                $worksheet->write($row_num, 2, $article['titre']);
                $worksheet->write($row_num, 3, $product['quantity']);            
                $worksheet->write($row_num, 4, $product['price']);
                $worksheet->write($row_num, 5, $product['priceold']);
                $row_num++;
            } 
        }        
    } 
    
    /**
     *  Upload Excel file and update products from it
     */
    public function UpdateExcel(&$message_ok = false, &$message_erreur = false) {
        try {
            $this->BackupExcel();
            $file_path = $this->uploadExcel();
            $this->updateProductsFromExcel($file_path);
            unlink($file_path); // Delete Excel file
        } catch (Exception $e) {
            $exception_message = $e->getMessage();
            $message_erreur = $exception_message ? $exception_message : _T('ecatalogue:form_error');
        }
        
        $message_ok = _T('ecatalogue:excel_products_updated');
    }
    
    /**
     *  Upload Excel file 
     *  @return string File path
     */
    private function uploadExcel() {
        include_spip('classes/pdk_upload');
        $config = array();
        $config['upload_path'] = _DIR_TMP;
        $config['allowed_types'] = 'xls|xlsx'; 
        $config['max_size'] = '2048';
        $config['encrypt_name'] = TRUE;
        $uploader = new pdk_Upload($config);
        
        if (!$uploader->do_upload('excel_products')) 
            throw new Exception(_T('ecatalogue:excel_file_upload_error')); 
            
        $file_data = $uploader->data();
        return $file_data['full_path'];
    }
    
    /**
     *  Update products from given Excel file
     *  @param string $file_path Excel file path
     */
    private function updateProductsFromExcel($file_path) {
        require_once _DIR_PLUGIN_EXCEL."pear/Spreadsheet/Excel/Reader.php";
        
        $reader = new Spreadsheet_Excel_Reader();
        $reader->setUTFEncoder('iconv');        
        $reader->setOutputEncoding('UTF-8');
        $reader->read($file_path);
        
        foreach($reader->sheets as $k=>$data) {
            // $reader->boundsheets[$k]['name']; // Sheet name            
            foreach($data["cells"] as $row) {
                $id_article = intval($row[1]); // First cell has index 1!
                if ($id_article) {
                    // Update product
                    $product = array();
                    $product['sku'] = $row["2"];
                    $product['titre'] = $row["3"];
                    $product['quantity'] = $row["4"];
                    $product['price'] = $row["5"];
                    $product['priceold'] = $row["6"];
                    if (!$this->product_mdl->update_by_id($id_article, $product, true))
                        throw new Exception("Error while updating product #".$id_article);
                    
                    // Update article status    
                    $quantity = intval($product['quantity']); 
                    if ($quantity > 0)
                        $statut = 'publie';
                    else
                        $statut = 'prepa';
                    if (!sql_updateq('spip_articles', array('statut'=>$statut,'titre'=>$product['titre']), 
                        "id_article=$id_article"))
                        throw new Exception("Error while updating article #".$id_article);        
                }               
            }
        }
    } 
} 
?>